В окружение для работы над проектом установлены библиотеки:
anyio==3.7.1
argon2-cffi==23.1.0
argon2-cffi-bindings==21.2.0
arrow==1.2.3
asttokens==2.2.1
async-lru==2.0.4
attrs==23.1.0
Babel==2.12.1
backcall==0.2.0
beautifulsoup4==4.12.2
bleach==6.0.0
catboost==1.2
certifi==2023.7.22
cffi==1.15.1
charset-normalizer==3.2.0
cloudpickle==2.2.1
colorama==0.4.6
comm==0.1.4
contourpy==1.1.0
cycler==0.11.0
debugpy==1.6.7.post1
decorator==5.1.1
defusedxml==0.7.1
exceptiongroup==1.1.3
executing==1.2.0
fastjsonschema==2.18.0
fonttools==4.42.1
fqdn==1.5.1
graphviz==0.20.1
greenlet==2.0.2
idna==3.4
importlib-metadata==6.8.0
importlib-resources==6.0.1
ipykernel==6.25.1
ipython==8.14.0
ipython-genutils==0.2.0
ipywidgets==8.1.0
isoduration==20.11.0
jedi==0.19.0
Jinja2==3.1.2
joblib==1.3.2
json5==0.9.14
jsonpointer==2.4
jsonschema==4.19.0
jsonschema-specifications==2023.7.1
jupyter==1.0.0
jupyter-console==6.6.3
jupyter-events==0.7.0
jupyter-lsp==2.2.0
jupyter_client==8.3.0
jupyter_core==5.3.1
jupyter_server==2.7.2
jupyter_server_terminals==0.4.4
jupyterlab==4.0.5
jupyterlab-pygments==0.2.2
jupyterlab-widgets==3.0.8
jupyterlab_server==2.24.0
kiwisolver==1.4.5
lightgbm==4.0.0
llvmlite==0.40.1
MarkupSafe==2.1.3
matplotlib==3.7.2
matplotlib-inline==0.1.6
mistune==3.0.1
nbclient==0.8.0
nbconvert==7.7.4
nbformat==5.9.2
nest-asyncio==1.5.7
notebook==7.0.2
notebook_shim==0.2.3
numba==0.57.1
numpy==1.23.5
overrides==7.4.0
packaging==23.1
pandas==2.0.3
pandocfilters==1.5.0
parso==0.8.3
phik==0.12.3
pickleshare==0.7.5
Pillow==10.0.0
platformdirs==3.10.0
plotly==5.16.1
prometheus-client==0.17.1
prompt-toolkit==3.0.39
psutil==5.9.5
psycopg2==2.9.7
pure-eval==0.2.2
pycparser==2.21
Pygments==2.16.1
pyparsing==3.0.9
python-dateutil==2.8.2
python-json-logger==2.0.7
pytz==2023.3
pywin32==306
pywinpty==2.0.11
PyYAML==6.0.1
pyzmq==25.1.1
qtconsole==5.4.3
QtPy==2.3.1
referencing==0.30.2
requests==2.31.0
rfc3339-validator==0.1.4
rfc3986-validator==0.1.1
rpds-py==0.9.2
scikit-learn==1.3.0
scipy==1.11.2
seaborn==0.12.2
Send2Trash==1.8.2
shap==0.42.1
six==1.16.0
slicer==0.0.7
sniffio==1.3.0
soupsieve==2.4.1
SQLAlchemy==2.0.20
stack-data==0.6.2
tenacity==8.2.3
terminado==0.17.1
threadpoolctl==3.2.0
tinycss2==1.2.1
tomli==2.0.1
tornado==6.3.3
tqdm==4.66.1
traitlets==5.9.0
typing_extensions==4.7.1
tzdata==2023.3
uri-template==1.3.0
urllib3==2.0.4
wcwidth==0.2.6
webcolors==1.13
webencodings==0.5.1
websocket-client==1.6.2
widgetsnbextension==4.0.8
zipp==3.16.2
Я — специалист по Data Sciense в каршеринговой компании.
Поступил заказ: нужно создать систему, которая могла бы оценить риск ДТП по выбранному маршруту движения.
Под риском понимается вероятность ДТП с любым повреждением транспортного средства. Как только водитель забронировал автомобиль, сел за руль и выбрал маршрут, система должна оценить уровень риска. Если уровень риска высок, водитель увидит предупреждение и рекомендации по маршруту.
Идея создания такой системы находится в стадии предварительного обсуждения и проработки. Чёткого алгоритма работы и подобных решений на рынке ещё не существует. Текущая задача — понять, возможно ли предсказывать ДТП, опираясь на исторические данные одного из регионов. Идея решения задачи от заказчика:
Создать модель предсказания ДТП (целевое значение — at_fault (виновник) в таблице parties)
Для модели выбрать тип виновника — только машина (car).
Выбрать случаи, когда ДТП привело к любым повреждениям транспортного средства, кроме типа SCRATCH (царапина).
Для моделирования ограничиться данными за 2012 год — они самые свежие.
Обязательное условие — учесть фактор возраста автомобиля.
На основе модели исследовать основные факторы ДТП.
Понять, помогут ли результаты моделирования и анализ важности факторов ответить на вопросы:
Возможно ли создать адекватную системы оценки водительского риска при выдаче авто?
Какие ещё факторы нужно учесть?
Нужно ли оборудовать автомобиль какими-либо датчиками или камерой?
Заказчик предлагает вам поработать с базой данных по происшествиям и сформировать свои идеи создания такой системы.
Инструкция по выполнению проекта
Шаг 1. Загрузите таблицы sql
Подключитесь к базе данных, используя данные:
db_config = {
'user': 'praktikum_student', # имя пользователя,
'pwd': 'Sdf4$2;d-d30pp', # пароль,
'host': 'rc1b-wcoijxj3yxfsf3fs.mdb.yandexcloud.net',
'port': 6432, # порт подключения,
'db': 'data-science-vehicle-db' # название базы данных,
}
Шаг 2. Проведите первичное исследование таблиц
Все ли таблицы имеют набор данных;
Соответствует ли количество таблиц условию задачи;
Имеется ли общий ключ для связи таблиц.
Для осмотра таблиц используйте sql-запрос.
Шаг 3. Проведите статистический анализ факторов ДТП
Выясните, в какие месяцы происходит наибольшее количество аварий. Проанализируйте весь период наблюдений (таблица collisions).
Создайте sql-запрос;
Постройте график;
Сделайте вывод.
Скоро состоится первое совещание вашей рабочей группы. Чтобы обсуждение было конструктивным, каждый сотрудник должен понимать данные. Для этого вы должны создать подходящие аналитические задачи и поручить их решение коллегам. Примеры задач:
Проведите анализ серьёзности повреждений транспортного средства, исходя из состояния дороги в момент ДТП (связать collisions и parties);
Найдите самые частые причины ДТП (таблица parties).
2.1. Создайте не менее шести задач для коллег. Опирайтесь на примеры и таблицы.
2.2. Пропишите порядок решения для двух задач из списка. Обязательное условие — решение этих задач должно включать связь не менее 2-х таблиц. Пример прописанного порядка:
Создайте sql-запрос;
Постройте график;
Сделайте вывод.
Шаг 4. Создайте модель для оценки водительского риска
Подготовьте набор данных на основе первичного предположения заказчика:
Выберите тип виновника — только машина (car). **
Возьмите случаи, когда ДТП привело к любым значимым повреждениям автомобиля любого из участников — все, кроме типа SCRATCH (царапина).
Для моделирования возьмите данные только за 2012 год.
Подготовка исходной таблицы должна проводиться с помощью sql-запроса.
Проведите первичный отбор факторов, необходимых для модели.
Изучите описание факторов. Нужно отобрать те, которые могут влиять на вероятность ДТП. Будет хорошо, если вы аргументируете свой выбор.
Пример:
columms =['party_type', # Тип участника происшествия. Таблица parties
'party_sobriety', # Уровень трезвости виновника (точно может влиять) Таблица parties
......
]
Проведите статистическое исследование отобранных факторов.
По результату исследовательского анализа внесите корректировки, если они нужны. Сделайте вывод.
Если необходимо, категоризируйте исходные данные, проведите масштабирование.
Подготовьте обучающую и тестовую выборки.
Шаг 5. Найдите лучшую модель
Смоделируйте не менее 3-х типов моделей с перебором гиперпараметров.
1–2 модели из спринта 2;
1–2 модели из спринта 3.
Выберите метрику для оценки модели, исходя из поставленной бизнесом задачи. Обоснуйте свой выбор.
Оформите вывод в виде сравнительной таблицы.
Шаг 6. Проверьте лучшую модель в работе
Проведите графический анализ «Матрица ошибок». Выведите полноту и точность на график.
Проанализируйте важность основных факторов, влияющих на вероятность ДТП.
Для одного из выявленных важных факторов проведите дополнительное исследование:
Покажите график зависимости фактора и целевой переменной.
Предложите, чем можно оборудовать автомобиль, чтобы учесть этот фактор во время посадки водителя.
Пример решения задачи 3:
Выявили, что самый важный фактор ДТП — уровень трезвости виновника party_sobriety. Из таблицы исходных данных известно: есть несколько уровней трезвости. Тогда решение по пунктам выглядит так:
Для графического анализа будем использовать столбчатую диаграмму. В ней отразим зависимость числа ДТП от уровня трезвости. Проанализируем график, сделаем выводы.
Предложить оборудовать автомобиль анализатором алкогольного опьянения. Измерение состояния при посадке сделать обязательным условием допуска за руль. А чтобы убедиться, что в трубку дышит именно водитель, добавить камеру, направленную на водительское место.
Шаг 7. Сделайте общий вывод по модели
Кратко опишите лучшую модель.
Сделайте вывод: насколько возможно создание адекватной системы оценки риска при выдаче авто?
Какие факторы ещё необходимо собирать, чтобы улучшить модель?
Оформление: Выполните задание в Jupyter Notebook. Заполните программный код в ячейках типа code, текстовые пояснения — в ячейках типа markdown. Примените форматирование и заголовки.
Краткое описание таблиц
collisions — общая информация о ДТП
Имеет уникальный case_id. Эта таблица описывает общую информацию о ДТП. Например, где оно произошло и когда.
parties — информация об участниках ДТП
Имеет неуникальный case_id, который сопоставляется с соответствующим ДТП в таблице collisions. Каждая строка здесь описывает одну из сторон, участвующих в ДТП. Если столкнулись две машины, в этой таблице должно быть две строки с совпадением case_id. Если нужен уникальный идентификатор, это case_id and party_number.
vehicles — информация о пострадавших машинах
Имеет неуникальные case_id и неуникальные party_number, которые сопоставляются с таблицей collisions и таблицей parties. Если нужен уникальный идентификатор, это case_id and party_number.
import time
!pip install shap
!pip install phik
import shap
import datetime
import phik
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
from sqlalchemy import create_engine
from sklearn.impute import SimpleImputer
from sklearn.model_selection import train_test_split, cross_val_score, GridSearchCV
from sklearn.preprocessing import StandardScaler, OneHotEncoder # , OrdinalEncoder
from sklearn.metrics import accuracy_score, recall_score, f1_score, roc_auc_score, roc_curve, precision_score
from sklearn.metrics import confusion_matrix, ConfusionMatrixDisplay, classification_report
from sklearn.tree import DecisionTreeClassifier
from sklearn.ensemble import RandomForestClassifier
from sklearn.linear_model import LogisticRegression
import lightgbm as lgb
from lightgbm import LGBMClassifier
import catboost
from catboost import CatBoostClassifier
import warnings
warnings.filterwarnings('ignore')
pd.options.display.max_columns = None
RANDOM_STATE=12345
#база данных чужая
db_config = {
'user': 'user',
'pwd': 'pwd',
'host': 'host',
'port': 'port',
'db': 'db'
}
connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(
db_config['user'],
db_config['pwd'],
db_config['host'],
db_config['port'],
db_config['db'],
)
engine = create_engine(connection_string)
collisions = '''
SELECT *
FROM collisions
LIMIT 10
'''
parties = '''
SELECT *
FROM parties
LIMIT 10
'''
vehicles = '''
SELECT *
FROM vehicles
LIMIT 10
'''
case_ids = '''
SELECT *
FROM case_ids
LIMIT 10
'''
pd.read_sql_query('''
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');
''', con = engine)
| table_name | |
|---|---|
| 0 | case_ids |
| 1 | collisions |
| 2 | parties |
| 3 | vehicles |
В базе данных 4 таблицы, что соответствует предоставленному описанию.
pd.read_sql_query('''
SELECT
table_name,
column_name,
data_type
FROM information_schema.columns
WHERE table_name IN ('case_ids', 'collisions', 'parties', 'vehicles');
''', con=engine)
| table_name | column_name | data_type | |
|---|---|---|---|
| 0 | case_ids | case_id | text |
| 1 | case_ids | db_year | text |
| 2 | collisions | case_id | text |
| 3 | collisions | county_city_location | text |
| 4 | collisions | county_location | text |
| 5 | collisions | distance | real |
| 6 | collisions | direction | text |
| 7 | collisions | intersection | integer |
| 8 | collisions | weather_1 | text |
| 9 | collisions | location_type | text |
| 10 | collisions | collision_damage | text |
| 11 | collisions | party_count | integer |
| 12 | collisions | primary_collision_factor | text |
| 13 | collisions | pcf_violation_category | text |
| 14 | collisions | type_of_collision | text |
| 15 | collisions | motor_vehicle_involved_with | text |
| 16 | collisions | road_surface | text |
| 17 | collisions | road_condition_1 | text |
| 18 | collisions | lighting | text |
| 19 | collisions | control_device | text |
| 20 | collisions | collision_date | date |
| 21 | collisions | collision_time | time without time zone |
| 22 | parties | id | integer |
| 23 | parties | case_id | text |
| 24 | parties | party_number | integer |
| 25 | parties | party_type | text |
| 26 | parties | at_fault | integer |
| 27 | parties | insurance_premium | integer |
| 28 | parties | party_sobriety | text |
| 29 | parties | party_drug_physical | text |
| 30 | parties | cellphone_in_use | integer |
| 31 | vehicles | id | integer |
| 32 | vehicles | case_id | text |
| 33 | vehicles | party_number | integer |
| 34 | vehicles | vehicle_type | text |
| 35 | vehicles | vehicle_transmission | text |
| 36 | vehicles | vehicle_age | integer |
collisions = pd.read_sql_query(collisions, con = engine)
collisions
| case_id | county_city_location | county_location | distance | direction | intersection | weather_1 | location_type | collision_damage | party_count | primary_collision_factor | pcf_violation_category | type_of_collision | motor_vehicle_involved_with | road_surface | road_condition_1 | lighting | control_device | collision_date | collision_time | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4083072 | 1942 | los angeles | 528.0 | north | 0 | cloudy | highway | small damage | 2 | vehicle code violation | unsafe lane change | sideswipe | other motor vehicle | wet | normal | daylight | none | 2009-01-22 | 07:25:00 |
| 1 | 4083075 | 4313 | santa clara | 0.0 | None | 1 | clear | None | small damage | 1 | vehicle code violation | improper passing | hit object | fixed object | dry | normal | dark with street lights | functioning | 2009-01-03 | 02:26:00 |
| 2 | 4083073 | 0109 | alameda | 0.0 | None | 1 | clear | None | scratch | 2 | vehicle code violation | improper turning | broadside | other motor vehicle | dry | normal | dark with street lights | functioning | 2009-01-11 | 03:32:00 |
| 3 | 4083077 | 0109 | alameda | 0.0 | None | 1 | clear | None | scratch | 2 | vehicle code violation | automobile right of way | broadside | other motor vehicle | dry | normal | daylight | functioning | 2009-01-11 | 10:35:00 |
| 4 | 4083087 | 4313 | santa clara | 0.0 | None | 1 | clear | None | scratch | 2 | vehicle code violation | speeding | rear end | other motor vehicle | dry | None | dark with street lights | functioning | 2009-01-02 | 22:43:00 |
| 5 | 4083097 | 0109 | alameda | 0.0 | None | 1 | clear | ramp | small damage | 2 | vehicle code violation | speeding | rear end | other motor vehicle | dry | normal | dark with street lights | functioning | 2009-01-18 | 02:18:00 |
| 6 | 4083092 | 1942 | los angeles | 1320.0 | south | 0 | clear | highway | scratch | 2 | vehicle code violation | unsafe lane change | rear end | other motor vehicle | dry | normal | daylight | none | 2009-01-21 | 10:30:00 |
| 7 | 4083096 | 1942 | los angeles | 200.0 | south | 0 | clear | highway | scratch | 3 | vehicle code violation | speeding | rear end | other motor vehicle | dry | normal | daylight | none | 2009-01-21 | 08:55:00 |
| 8 | 4083100 | 1942 | los angeles | 2112.0 | north | 0 | cloudy | highway | scratch | 2 | vehicle code violation | speeding | rear end | other motor vehicle | dry | normal | daylight | none | 2009-01-21 | 16:20:00 |
| 9 | 4083103 | 4313 | santa clara | 0.0 | None | 1 | cloudy | None | scratch | 2 | vehicle code violation | None | broadside | other motor vehicle | wet | normal | dusk or dawn | functioning | 2009-01-02 | 15:55:00 |
parties = pd.read_sql_query(parties, con = engine)
parties
| id | case_id | party_number | party_type | at_fault | insurance_premium | party_sobriety | party_drug_physical | cellphone_in_use | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 22 | 3899454 | 1 | road signs | 1 | 29.0 | had not been drinking | None | 0.0 |
| 1 | 23 | 3899454 | 2 | road signs | 0 | 7.0 | had not been drinking | None | 0.0 |
| 2 | 29 | 3899462 | 2 | car | 0 | 21.0 | had not been drinking | None | 0.0 |
| 3 | 31 | 3899465 | 2 | road signs | 0 | 24.0 | had not been drinking | None | 0.0 |
| 4 | 41 | 3899478 | 2 | road bumper | 0 | NaN | not applicable | not applicable | 0.0 |
| 5 | 43 | 3899481 | 2 | road bumper | 0 | NaN | not applicable | not applicable | 0.0 |
| 6 | 72 | 3899505 | 2 | road bumper | 0 | NaN | not applicable | not applicable | 0.0 |
| 7 | 73 | 3899505 | 3 | road bumper | 0 | NaN | not applicable | not applicable | 0.0 |
| 8 | 76 | 3899509 | 1 | road bumper | 0 | NaN | had not been drinking | None | 0.0 |
| 9 | 93 | 3981292 | 2 | road bumper | 0 | NaN | had not been drinking | None | NaN |
vehicles = pd.read_sql_query(vehicles, con = engine)
vehicles
| id | case_id | party_number | vehicle_type | vehicle_transmission | vehicle_age | |
|---|---|---|---|---|---|---|
| 0 | 1175713 | 5305032 | 2 | sedan | manual | 3 |
| 1 | 1 | 3858022 | 1 | sedan | auto | 3 |
| 2 | 1175712 | 5305030 | 1 | sedan | auto | 3 |
| 3 | 1175717 | 5305033 | 3 | sedan | auto | 5 |
| 4 | 1175722 | 5305034 | 2 | sedan | auto | 5 |
| 5 | 1175737 | 5305046 | 2 | sedan | auto | 5 |
| 6 | 1175750 | 5305053 | 3 | sedan | auto | 9 |
| 7 | 1175715 | 5305033 | 1 | sedan | manual | 10 |
| 8 | 1175716 | 5305033 | 2 | sedan | manual | 4 |
| 9 | 1175758 | 5305060 | 2 | sedan | auto | 11 |
case_ids = pd.read_sql_query(case_ids, con = engine)
case_ids
| case_id | db_year | |
|---|---|---|
| 0 | 0081715 | 2021 |
| 1 | 0726202 | 2021 |
| 2 | 3858022 | 2021 |
| 3 | 3899441 | 2021 |
| 4 | 3899442 | 2021 |
| 5 | 3899445 | 2021 |
| 6 | 3899446 | 2021 |
| 7 | 3899449 | 2021 |
| 8 | 3899450 | 2021 |
| 9 | 3899453 | 2021 |
Все таблицы имеют какие-то данные.
Общий ключ для связи таблиц.
query = '''
SELECT constraint_name,
table_name,
column_name
FROM information_schema.key_column_usage
'''
df = pd.read_sql_query(query, con=engine)
df
| constraint_name | table_name | column_name | |
|---|---|---|---|
| 0 | case_ids_pk | case_ids | case_id |
| 1 | parties_pk | parties | id |
| 2 | vehicles_pk | vehicles | id |
| 3 | collisions_case_ids_case_id_fk | collisions | case_id |
| 4 | parties_case_ids_case_id_fk | parties | case_id |
| 5 | vehicles_case_ids_case_id_fk | vehicles | case_id |
У collisions нет первичного ключа, но в описании БД сказано, что case_id уникален для каждой записи. Case_id будет тем самым ключом для связи всех таблиц, он есть в каждой таблице.
Подключение к базе данных прошло успешно. В базе данных заявленные таблицы присутствуют, данные в них содержатся, где-то есть пропуски. Связывать данные можно по полю case_id.
Выясним, в какие месяцы происходит наибольшее количество аварий, проанализировав весь период наблюдений.
query = '''
with a as (SELECT COUNT(case_id) AS total_ids,
DATE_TRUNC('month', collision_date)::date AS month
FROM collisions
GROUP BY DATE_TRUNC('month', collision_date)
ORDER BY DATE_TRUNC('month', collision_date))
SELECT *,
EXTRACT(year FROM CAST(DATE_TRUNC('year', month) as DATE)) AS year,
EXTRACT(month FROM CAST(DATE_TRUNC('month', month) as DATE)) AS month_only
FROM a
;
'''
df = pd.read_sql_query(query, con=engine)
fig = px.bar(
df, x='month', y='total_ids',
barmode='group',
title=('Статистика дтп по месяцам')
)
fig.show()
Довольно много данных с января 2009 г до мая 2012 г включительно, дальше данных совсем немного.
Апрель-май 2012 - непонятное снижение кольчества аварий, что-то по ним решать не стоит, наверное. Январь-февраль 2010-2012 показывают снижение числа аварий на фоне других месяцев, июни 2009-2011 тоже демонстрируют просадку по количеству аварий. Март, май, октябрь, декабрь - рост числа аварий, при этом максимум приходится на октябри.
Посмотрим на числа - что там после мая 2012.
dt = datetime.date(2012, 1, 1)
df.loc[df['month']>=dt]
| total_ids | month | year | month_only | |
|---|---|---|---|---|
| 36 | 32020 | 2012-01-01 | 2012.0 | 1.0 |
| 37 | 30377 | 2012-02-01 | 2012.0 | 2.0 |
| 38 | 32498 | 2012-03-01 | 2012.0 | 3.0 |
| 39 | 29143 | 2012-04-01 | 2012.0 | 4.0 |
| 40 | 25168 | 2012-05-01 | 2012.0 | 5.0 |
| 41 | 3062 | 2012-06-01 | 2012.0 | 6.0 |
| 42 | 390 | 2012-07-01 | 2012.0 | 7.0 |
| 43 | 622 | 2012-08-01 | 2012.0 | 8.0 |
| 44 | 510 | 2012-09-01 | 2012.0 | 9.0 |
| 45 | 304 | 2012-10-01 | 2012.0 | 10.0 |
| 46 | 253 | 2012-11-01 | 2012.0 | 11.0 |
| 47 | 200 | 2012-12-01 | 2012.0 | 12.0 |
| 48 | 144 | 2013-01-01 | 2013.0 | 1.0 |
| 49 | 108 | 2013-02-01 | 2013.0 | 2.0 |
| 50 | 46 | 2013-03-01 | 2013.0 | 3.0 |
| 51 | 5 | 2013-04-01 | 2013.0 | 4.0 |
| 52 | 1 | 2013-06-01 | 2013.0 | 6.0 |
| 53 | 1 | 2013-07-01 | 2013.0 | 7.0 |
| 54 | 1 | 2020-03-01 | 2020.0 | 3.0 |
| 55 | 1 | 2020-07-01 | 2020.0 | 7.0 |
С 13 по 20 год вообще ничего. Какие-то выводы стоит делать на данных до марта 2012. Нам не известно факторов, которые могли бы так резко, как после марта 2012 года, повлиять на количество аварий. Скорее всего, вводили какой-то новый сервис регистрации аварий, и этой базой перестали пользоваться.
Посмотрим по годам, как развивается ситуация на дорогах.
query = """
WITH f AS
(SELECT CAST(DATE_TRUNC('MONTH', COLLISION_DATE) as DATE) AS date,
COUNT(CASE_ID)
FROM collisions
WHERE COLLISION_DATE BETWEEN '2009-01-01' AND '2011-12-31'
GROUP BY CAST(DATE_TRUNC('MONTH', COLLISION_DATE) as DATE))
SELECT EXTRACT(MONTH FROM date) AS month,
AVG(count) AS total_ids
FROM f
GROUP BY EXTRACT(MONTH FROM date);
"""
df_mid = pd.read_sql_query(query, con = engine)
df_mid = df_mid.sort_values(by = 'month')
df = df.sort_values(by = 'month')
month_list = {1 : 'Январь',
2 : 'Февраль',
3 : 'Март',
4 : 'Апрель',
5 : 'Май',
6 : 'Июнь',
7 : 'Июль',
8 : 'Август',
9 : 'Сентябрь',
10 : 'Октябрь',
11 : 'Ноябрь',
12 : 'Декабрь'}
df['month_only'] = df['month_only'].replace(month_list)
df_mid['month'] = df_mid['month'].replace(month_list)
fig, ax = plt.subplots()
fig.set_figwidth(15)
fig.set_figheight(7)
ax.plot(df.loc[df['year']==2009]['month_only'], df.loc[df['year']==2009]['total_ids'], label='2009')
ax.plot(df.loc[df['year']==2009]['month_only'], df.loc[df['year']==2010]['total_ids'], label='2010')
ax.plot(df.loc[df['year']==2009]['month_only'], df.loc[df['year']==2011]['total_ids'], label='2011')
ax.plot(df_mid['month'], df_mid['total_ids'], linewidth=16, alpha=0.3, label='Среднее')
ax.set_title('ДТП втечение года', fontsize=20)
ax.set_xlabel('Месяц проишествия')
ax.set_ylabel('Количество проишествий')
ax.legend()
plt.grid(linestyle='--')
plt.show()
Минимумы в феврале, конечно, могут быть из-за меньшего количества дней. Но точно падения идут в апреле, июне и ноябре, рост - март, май, октябрь, декабрь. Март и октябрь - месяцы непогоды, межсезонье, перепады темпераур втечение дня могут быть фактором риска. Декабрь, май - не знаю, что там происходит. Возможно, люди чаще выезжают в гости - весной тепло, можно на пикник, зимой - новогодние праздники, к ним готовятся, по магазинам ходят, волнуются.
Ещё можно заметить, что от года к году число аварий снижается, особенно весной.
Октябрь - самый аварийный месяц. За ним - декабрь, май и март.
Для предстоящей встречи рабочей группы коллег необходимо ознакомить с данными. Для этого надо составить несколько дополнительных задач.
Предлагаю посмотреть на следующие связи:
1 Сумма страховых выплат в зависимости от возраста автомобиля.
2 Анализ серьёзности повреждений ТС, исходя из состояния водителя.
3 Зависимость серьёзности повреждений ТС от коробки передач.
4 Анализ серьёзности повреждений ТС, исходя из типа участника ДТП.
5 Тип кузова и трезвость участника - какие машины чаще выбирают нетрезвые люди.
6 Тяжесть повреждения связана ли с возрастом автомобиля?
7 В каких районах больше пьяных водителей.
8 Влияет ли направление движения на виновность?
9 Зависимость серьёзности повреждений от основного фактора нарушения.
Рассмотрим некоторые из них сами.
Нам важно определить безопасность маршрута, поэтому рассмотрим наиболее аварийно-опасные регионы. Города представлены в числовом виде, это будет непонятно без словаря "индексов", поэтому посмотрим на районы.
query = '''
SELECT COUNTY_LOCATION,
COUNT(CASE_ID)
FROM collisions
GROUP BY COUNTY_LOCATION
ORDER BY COUNT(CASE_ID) DESC;
'''
df = pd.read_sql_query(query, con = engine)
df.sample(8)
| county_location | count | |
|---|---|---|
| 22 | santa cruz | 11570 |
| 56 | alpine | 290 |
| 34 | nevada | 4121 |
| 35 | imperial | 3955 |
| 20 | solano | 14412 |
| 6 | sacramento | 56988 |
| 40 | yuba | 2327 |
| 53 | mariposa | 810 |
plt.figure(figsize=(8, 15))
sns.barplot(data=df, y="county_location", x="count", orient='h', color='g')
plt.title('Районы с наибольшим количеством ДТП', fontsize=15)
#plt.xticks( fontsize=10)
#plt.yticks( fontsize=10)
plt.ylabel('Наименование района')
plt.xlabel('Количество проишествий')
plt.grid(True)
plt.show()
Что же происходит в самых опасных регионах?
df = '''
WITH
c1 AS
(SELECT COUNTY_LOCATION,
COUNT(CASE_ID)
FROM collisions
GROUP BY COUNTY_LOCATION
ORDER BY COUNT(CASE_ID) DESC),
c2 AS
(SELECT ROW_NUMBER() OVER (PARTITION BY COUNTY_LOCATION ORDER BY COUNT(CASE_ID) DESC),
COUNT(CASE_ID),
COUNTY_LOCATION,
PCF_VIOLATION_CATEGORY
FROM collisions
GROUP BY COUNTY_LOCATION, PCF_VIOLATION_CATEGORY
ORDER BY COUNTY_LOCATION)
SELECT COUNTY_LOCATION,
PCF_VIOLATION_CATEGORY,
COUNT
FROM c2
WHERE ROW_NUMBER <= 3 AND COUNTY_LOCATION IN (SELECT COUNTY_LOCATION
FROM c1
LIMIT 8);
'''
df = pd.read_sql_query(df, con = engine)
df
| county_location | pcf_violation_category | count | |
|---|---|---|---|
| 0 | alameda | speeding | 20721 |
| 1 | alameda | improper turning | 13408 |
| 2 | alameda | unsafe lane change | 5490 |
| 3 | los angeles | speeding | 135566 |
| 4 | los angeles | improper turning | 58083 |
| 5 | los angeles | automobile right of way | 57609 |
| 6 | orange | speeding | 42328 |
| 7 | orange | improper turning | 16047 |
| 8 | orange | automobile right of way | 13276 |
| 9 | riverside | speeding | 22392 |
| 10 | riverside | improper turning | 13402 |
| 11 | riverside | automobile right of way | 6798 |
| 12 | sacramento | speeding | 20381 |
| 13 | sacramento | improper turning | 9011 |
| 14 | sacramento | automobile right of way | 6386 |
| 15 | san bernardino | speeding | 24488 |
| 16 | san bernardino | improper turning | 14538 |
| 17 | san bernardino | automobile right of way | 9898 |
| 18 | san diego | speeding | 24890 |
| 19 | san diego | improper turning | 15368 |
| 20 | san diego | automobile right of way | 7379 |
| 21 | santa clara | speeding | 20745 |
| 22 | santa clara | improper turning | 7188 |
| 23 | santa clara | unsafe lane change | 4084 |
plt.figure(figsize=(15, 8))
sns.barplot(data=df, x="county_location", y="count", hue = 'pcf_violation_category')
plt.title('Категории нарушений по самым аварийным регионам', fontsize=20)
#plt.xticks(rotation=90)
plt.xlabel('Наименование района')
plt.ylabel('Количество проишествий')
plt.grid(True)
plt.show()
Во всех регионах на первом месте скорость, на втором - неправильный поворот. Но не во всех право проезда создаёт проблемы, вместо него - перестроение в двух регионах.
query = '''
SELECT c.collision_damage,
p.party_drug_physical
FROM collisions AS c
JOIN parties AS p ON c.case_id = p.case_id
WHERE c.collision_damage IN ('small damage', 'scratch')
'''
df = pd.read_sql_query(query, con=engine)
df.sample(4)
| collision_damage | party_drug_physical | |
|---|---|---|
| 1801092 | small damage | None |
| 1147588 | small damage | None |
| 1849654 | small damage | None |
| 539362 | small damage | None |
df.isna().sum()
collision_damage 0 party_drug_physical 1975744 dtype: int64
df = df.dropna()
query = '''
SELECT c.collision_damage,
p.party_drug_physical
FROM collisions AS c
JOIN parties AS p ON c.case_id = p.case_id
WHERE c.collision_damage IN ('fatal', 'severe damage', 'middle damage')
'''
df2 = pd.read_sql_query(query, con=engine)
df2.sample(4)
| collision_damage | party_drug_physical | |
|---|---|---|
| 158626 | middle damage | None |
| 206352 | middle damage | None |
| 69673 | middle damage | None |
| 150038 | severe damage | None |
df2.isna().sum()
collision_damage 0 party_drug_physical 344376 dtype: int64
df2 = df2.dropna()
fig = px.histogram(
df, color='party_drug_physical', x='collision_damage',
barmode='group',
title='Зависимость тяжести ДТП от физического состояния водителя',
histfunc='count'
)
fig.show()
Видим, что для небольших и средних(ниже график) повреждений основным источником опасности является усталость.
fig = px.histogram(
df2, color='party_drug_physical', x='collision_damage',
barmode='group',
title='Зависимость тяжести ДТП от физического состояния водителя (количество)',
histfunc='count'
)
fig.show()
Фатальные и сильные повреждения выделяются тем, что они в первую очередь случаются из-за воздействия лекарств на водителя. И здесь имеются ввиду любые препараты, в том числе выписанные врачом официально для лечения.
query = '''
SELECT c.COUNTY_LOCATION,
COUNT(c.CASE_ID)
FROM collisions AS c
JOIN parties AS p ON c.case_id = p.case_id
WHERE p.PARTY_SOBRIETY in ('had been drinking, under influence',
'had been drinking, impairment unknown',
'had been drinking, not under influence')
GROUP BY c.COUNTY_LOCATION
order by COUNT desc
'''
df = pd.read_sql_query(query, con=engine)
df.head(8)
| county_location | count | |
|---|---|---|
| 0 | los angeles | 40725 |
| 1 | orange | 13107 |
| 2 | san diego | 10870 |
| 3 | san bernardino | 8585 |
| 4 | riverside | 8182 |
| 5 | alameda | 5716 |
| 6 | sacramento | 5544 |
| 7 | santa clara | 4874 |
plt.figure(figsize=(8,15))
sns.barplot(data=df, y="county_location", x="count", orient='h', color='g')
plt.title('Районы с наибольшим количеством ДТП с участием водителей в состоянии алкогольного опьянения', fontsize=12)
plt.ylabel('Наименование района')
plt.xlabel('Количество проишествий')
plt.grid(True)
plt.show()
Картина повторяется.
query = '''
with a as
(SELECT c.COUNTY_LOCATION,
p.PARTY_SOBRIETY,
c.CASE_ID
FROM collisions AS c
JOIN parties AS p ON c.case_id = p.case_id),
b as
(select COUNT(CASE_ID),
COUNTY_LOCATION,
PARTY_SOBRIETY
FROM a
WHERE PARTY_SOBRIETY in ('had been drinking, under influence',
'had been drinking, impairment unknown',
'had been drinking, not under influence')
GROUP BY COUNTY_LOCATION, PARTY_SOBRIETY),
c as
(select COUNTY_LOCATION, sum(count)
from b
GROUP BY COUNTY_LOCATION
order by sum desc
limit 8
)
select
COUNT,
COUNTY_LOCATION,
PARTY_SOBRIETY
from b
where COUNTY_LOCATION in (select county_location from c)
'''
df = pd.read_sql_query(query, con=engine)
plt.figure(figsize=(15, 8))
sns.barplot(data=df, x="county_location", y="count", hue = 'party_sobriety')
plt.title('Регионы с максимальным количеством аварий с участием водителей в состоянии алкогольного опьянения', fontsize=12)
#plt.xticks(rotation=90)
plt.xlabel('Наименование района')
plt.ylabel('Количество проишествий')
plt.grid(True)
plt.show()
plt.figure(figsize=(15, 8))
sns.barplot(data=df, hue="county_location", y="count", x = 'party_sobriety')
plt.title('Регионы с максимальным количеством аварий с участием водителей в состоянии алкогольного опьянения', fontsize=12)
#plt.xticks(rotation=90)
plt.xlabel('Вид опьянения')
plt.ylabel('Количество проишествий')
plt.grid(True)
plt.show()
Сочетание факторов наиболее опасно. Предложение в начале поездки подышать в трубочку газоанализатора было бы неплохой идеей.
Например, в утренние и вечерние часы Солнце может слепить из-за низкого расположения над горизонтом, и направления движения запад и восток будут фиксироваться немного чаще. Тогда нужно думать о поляризации лобового стекла, например.
query = """
with foltdir as (
select p.at_fault,
c.direction
from parties as p
join collisions as c on p.case_id=c.case_id
WHERE extract(hour from COLLISION_TIME) in (5, 6, 7, 8, 17, 18,19, 20)
)
select sum(at_fault),
direction
from foltdir
WHERE direction is NOT null
group by direction
"""
df = pd.read_sql_query(query, con = engine)
df
| sum | direction | |
|---|---|---|
| 0 | 81360 | west |
| 1 | 97140 | south |
| 2 | 97046 | north |
| 3 | 81523 | east |
fig, ax = plt.subplots()
fig.set_figwidth(15)
fig.set_figheight(7)
ax.plot(df['direction'], df['sum'])
ax.set_title('Временной ряд ДТП', fontsize=20)
ax.set_xlabel('Месяц проишествия')
ax.set_ylabel('Среднее количество проишествий')
plt.show()
Данные показывают обратную выдвинутой гипотезе ситуацию.
query = """
with foltdir as (
select p.at_fault,
c.direction
from parties as p
join collisions as c on p.case_id=c.case_id
WHERE LIGHTING in ('dusk or dawn')
)
select sum(at_fault),
direction
from foltdir
WHERE direction is NOT null
group by direction
"""
df = pd.read_sql_query(query, con = engine)
fig, ax = plt.subplots()
fig.set_figwidth(15)
fig.set_figheight(7)
ax.plot(df['direction'], df['sum'])
ax.set_title('Временной ряд ДТП', fontsize=20)
ax.set_xlabel('Месяц проишествия')
ax.set_ylabel('Среднее количество проишествий')
plt.show()
Если отобрать по освещённости и значению в ней "закат, восход", то всё равно ничего не меняется.
Направления север-юг в рассветные и закатные часы более опасны, чем запад-восток. Интересно, это видимость связи, или есть адекватная причина?
АКПП позволяет меньше трепать машину ручным управлением, за вас половину работы делает робот. Сохранность этого механизма может ли привезти к меньшим потерям в аварии, может, способ тормозить на автомате влияет на авариность?
query = '''
select c.collision_damage,
v.vehicle_transmission
from vehicles as v
join collisions as c on v.case_id=c.case_id
where v.vehicle_transmission is not null
'''
df = pd.read_sql_query(query, con=engine)
fig = px.histogram(
df, x='collision_damage', color='vehicle_transmission',
barmode='group',
title='Зависимость тяжести ДТП от типа трансмиссии',
histfunc='count',
category_orders={
'vehicle_transmission': ['auto', 'manual']
}
)
fig.show()